﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for VYC23BO
    /// </summary>
    public class VYC23BO:DBConnectionBO
    {
        public VYC23BO():base()
        {
            
        }
        public List<ModelVYC23> GetAllList()
        {
            var list = new List<ModelVYC23>();
            const string query = "SELECT [Id],[IdSubLoaiChuyenMuc],[IdSubLoaiTour],[TieuDe],[TomTat],[LienKet],[IdDiadiem],[NgayCapNhat],[TrangThai] FROM [VYC-23]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC23();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? string.Empty : dataTable.Rows[i][0].ToString();
                model.IdSubLoaiChuyenMuc = (dataTable.Rows[i][1] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][1]);
                model.IdSubLoaiTour = (dataTable.Rows[i][2] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][2]);
                model.TieuDe = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                model.TomTat = (dataTable.Rows[i][4] == DBNull.Value) ? string.Empty : dataTable.Rows[i][4].ToString();
                model.LienKet = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                model.IdDiadiem = (dataTable.Rows[i][6] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][6]);
                model.NgayCapNhat = (dataTable.Rows[i][7] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][7];
                model.TrangThai = (dataTable.Rows[i][8] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][8]);

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Add new a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddData(ModelVYC23 model)
        {
            var query = "INSERT INTO [VYC-23]([Id],[IdSubLoaiChuyenMuc],[IdSubLoaiTour],[TieuDe],[TomTat],[LienKet],[IdDiadiem],[NgayCapNhat],[TrangThai]) VALUES (@id, @idsubloaichuyenmuc,@idsubloaitour, @tieude, @tomtat, @lienket, @iddiadiem, GETDATE(), @trangthai)";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.VarChar).Value = model.Id;
            sqlCommand.Parameters.Add("@idsubloaichuyenmuc", SqlDbType.Int).Value = model.IdSubLoaiChuyenMuc;
            sqlCommand.Parameters.Add("@idsubloaitour", SqlDbType.Int).Value = model.IdSubLoaiTour;
            sqlCommand.Parameters.Add("@tieude", SqlDbType.NVarChar).Value = model.TieuDe;
            sqlCommand.Parameters.Add("@tomtat", SqlDbType.NVarChar).Value = model.TomTat;
            sqlCommand.Parameters.Add("@lienket", SqlDbType.VarChar).Value = model.LienKet;
            sqlCommand.Parameters.Add("@iddiadiem", SqlDbType.Int).Value = model.IdDiadiem;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int EditData(ModelVYC23 model)
        {
            var query = "UPDATE [VYC-23] SET " +
                        "[IdSubLoaiChuyenMuc] = @idsubloaichuyenmuc" +
                        "[IdSubLoaiTour] = @idsubloaitour" +
                        "[TieuDe] = @tieude" +
                        "[TomTat] = @tomtat" +
                        "[LienKet] = @lienket" +
                        "[IdDiadiem] = @iddiadiem" +
                        "[NgayCapNhat] = GETDATE(), [TrangThai] = @trangthai" +
                        " WHERE Id like @id";
            var sqlCommand = new SqlCommand(query, connection);

            sqlCommand.Parameters.Add("@id", SqlDbType.VarChar).Value = model.Id;
            sqlCommand.Parameters.Add("@idsubloaichuyenmuc", SqlDbType.Int).Value = model.IdSubLoaiChuyenMuc;
            sqlCommand.Parameters.Add("@idsubloaitour", SqlDbType.Int).Value = model.IdSubLoaiTour;
            sqlCommand.Parameters.Add("@tieude", SqlDbType.NVarChar).Value = model.TieuDe;
            sqlCommand.Parameters.Add("@tomtat", SqlDbType.NVarChar).Value = model.TomTat;
            sqlCommand.Parameters.Add("@lienket", SqlDbType.VarChar).Value = model.LienKet;
            sqlCommand.Parameters.Add("@iddiadiem", SqlDbType.Int).Value = model.IdDiadiem;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;

            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(ModelVYC23 model)
        {
            var query = "DELETE FROM [VYC-23] " +
                        " WHERE Id like '" + model.Id + "'";
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Get by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<ModelVYC23> GetById(string id)
        {
            var list = new List<ModelVYC23>();
            var query = "SELECT [Id],[IdSubLoaiChuyenMuc],[IdSubLoaiTour],[TieuDe],[TomTat],[LienKet],[IdDiadiem],[NgayCapNhat],[TrangThai] FROM [VYC-23] WHERE Id like '" + id + "'";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC23();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? string.Empty : dataTable.Rows[i][0].ToString();
                model.IdSubLoaiChuyenMuc = (dataTable.Rows[i][1] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][1]);
                model.IdSubLoaiTour = (dataTable.Rows[i][2] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][2]);
                model.TieuDe = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                model.TomTat = (dataTable.Rows[i][4] == DBNull.Value) ? string.Empty : dataTable.Rows[i][4].ToString();
                model.LienKet = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                model.IdDiadiem = (dataTable.Rows[i][6] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][6]);
                model.NgayCapNhat = (dataTable.Rows[i][7] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][7];
                model.TrangThai = (dataTable.Rows[i][8] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][8]);

                list.Add(model);
            }
            return list;
        }

        public List<ModelVYC23> GetLimitVideoForModule(int trangthai)
        {
            var list = new List<ModelVYC23>();
            try
            {
                var query = "SELECT TOP 10 * FROM [VYC-23] WHERE TrangThai = @trangthai Order by NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);                
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    var model = new ModelVYC23();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);                                        
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.IdDiadiem = Convert.ToInt32(reader["IdDiadiem"]);
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }

        public List<ModelVYC23> GetLimitVideoForTourDetail(int trangthai, int idsubloaitour)
        {
            var list = new List<ModelVYC23>();
            try
            {
                var query = "SELECT TOP 10 * FROM [VYC-23] WHERE IdSubLoaiTour = @idsubloaitour and TrangThai = @trangthai Order by NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@idsubloaitour", SqlDbType.Int).Value = idsubloaitour;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    var model = new ModelVYC23();

                    model.Id = reader["Id"].ToString();
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdSubLoaiTour = Convert.ToInt32(reader["IdSubLoaiTour"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.IdDiadiem = Convert.ToInt32(reader["IdDiadiem"]);
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }
    }
}